package com.skyworth.web.order.repository;

import com.skyworth.web.order.domain.OrderModel;
import com.skyworth.web.order.domain.ParentOrderModel;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.List;

/**
 * Created by skyworth on 2016/6/23.
 */
public interface ParentOrderRepository extends PagingAndSortingRepository<ParentOrderModel, Long> {


    @Query(value = "SELECT * FROM ff_parent_order WHERE (state = 0 or state = 2 or state =3) AND user_id=?1 ORDER BY state ASC,create_date DESC LIMIT 0,1",nativeQuery = true)
    ParentOrderModel getLastByTableId(String userId);

    @Query(value = "SELECT * FROM ff_parent_order WHERE state = 1 AND user_id=?1 ORDER BY create_date DESC LIMIT 0,1",nativeQuery = true)
    ParentOrderModel getParentOrderByTableId(String userId);

    @Modifying
    @Query("update ParentOrderModel m set m.state =1 where m.state=0 and m.userId = ?2")
    void updateOrderState(String userId);

    @Modifying
    @Query("update ParentOrderModel m set m.state =8 where m.id=?1 and m.state=1")
    void deleteParentOrder(long id);

    @Modifying
    @Query("update ParentOrderModel m set m.state =9 where m.id=?1 and (m.state=0 or m.state=2)")
    void waiterDeleteParentOrder(long id);

    @Modifying
    @Query("update ParentOrderModel m set m.state =5,m.userId=?2 where m.id=?1 and m.state=0")
    void offlineParentOrderPay(long id,String userId);

    @Query(value = "select c.`name` storeName,a.id orderId,a.people people,d.`name` tableName,date_format(a.create_date,'%Y-%m-%d %H:%i:%s') createDate,sum(binary b.original_price) originalPrice,sum(binary b.price) price,a.store_id storeId,a.table_id tableId,a.user_id userId,a.state state from ff_parent_order a inner join ff_order b on a.id=b.parent_id left join ff_store c on a.store_id=c.id left join ff_table d on a.table_id=d.id where a.state=0 and b.state=0 and a.user_id=?1 group by a.id limit 1",nativeQuery = true)
    Object getCurrentTableNoPayOrder(String userId);

    @Query(value = "select b.id subId from ff_parent_order a inner join ff_order b on a.id=b.parent_id where a.state=0 and b.state=0 and a.user_id=?1 order by b.id asc",nativeQuery = true)
    List<Long> getCurrentTableNoPaySubOrderId(String userId);

    @Query(value = "select c.`name` storeName,a.id orderId,a.people people,d.`name` tableName,date_format(a.create_date,'%Y-%m-%d %H:%i:%s') createDate,sum(binary b.original_price) originalPrice,sum(binary b.price) price,a.store_id storeId,a.table_id tableId,a.user_id userId,a.state state from ff_parent_order a inner join ff_order b on a.id=b.parent_id left join ff_store c on a.store_id=c.id left join ff_table d on a.table_id=d.id where a.id=?1 group by a.id limit 1",nativeQuery = true)
    Object getParentOrderDetail(long id);

    @Query(value = "select c.`name` storeName,a.id orderId,a.people people,d.`name` tableName,date_format(a.create_date,'%Y-%m-%d %H:%i:%s') createDate,sum(binary b.original_price) originalPrice,sum(binary b.price) price,a.store_id storeId,a.table_id tableId,a.user_id userId,a.state state from ff_parent_order a inner join ff_order b on a.id=b.parent_id left join ff_store c on a.store_id=c.id left join ff_table d on a.table_id=d.id where a.id=?1 and a.state=1 and b.state=1 group by a.id limit 1",nativeQuery = true)
    Object getParentPayOrderDetail(long id);

    @Query(value = "select c.`name` storeName,a.id orderId,a.people people,d.`name` tableName,date_format(a.create_date,'%Y-%m-%d %H:%i:%s') createDate,sum(binary b.original_price) originalPrice,sum(binary b.price) price,a.store_id storeId,a.table_id tableId,a.user_id userId,a.state state from ff_parent_order a inner join ff_order b on a.id=b.parent_id left join ff_store c on a.store_id=c.id left join ff_table d on a.table_id=d.id where a.id=?1 and a.state=0 and b.state=0 group by a.id limit 1",nativeQuery = true)
    Object getParentNoPayOrderDetail(long id);

    @Query(value = "select count(1) from (select a.id from ff_parent_order a inner join ff_order b on a.id=b.parent_id left join ff_store c on a.store_id=c.id left join ff_table d on a.table_id=d.id where a.state=1 and b.state=1 and a.user_id=?1 group by a.id) s",nativeQuery = true)
    long getUserHistoryOrderTotal(String userId);

    @Query(value = "select c.`name` storeName,a.id orderId,a.people people,d.`name` tableName,date_format(a.create_date,'%Y-%m-%d %H:%i:%s') createDate,sum(binary b.original_price) originalPrice,sum(binary b.price) price,a.store_id storeId,a.table_id tableId,a.user_id userId,a.state from ff_parent_order a inner join ff_order b on a.id=b.parent_id left join ff_store c on a.store_id=c.id left join ff_table d on a.table_id=d.id where a.state=1 and b.state=1 and a.user_id=?1 group by a.id order by createDate desc limit ?2,?3",nativeQuery = true)
    List<Object> getUserHistoryOrder(String userId,int starNumber,int endNumber);



    @Query(value = "select count(1) from (select a.id from ff_parent_order a inner join ff_order b on a.id=b.parent_id left join ff_store c on a.store_id=c.id left join ff_table d on a.table_id=d.id where a.state=1 and b.state=1 and a.store_id=?1 group by a.id) s",nativeQuery = true)
    long getStoreHistoryOrderTotal(long storeId);

    @Query(value = "select c.`name` storeName,a.id orderId,a.people people,d.`name` tableName,date_format(a.create_date,'%Y-%m-%d %H:%i:%s') createDate,sum(binary b.original_price) originalPrice,sum(binary b.price) price from ff_parent_order a inner join ff_order b on a.id=b.parent_id left join ff_store c on a.store_id=c.id left join ff_table d on a.table_id=d.id where a.state=1 and b.state=1 and a.store_id=?1 group by a.id order by createDate desc limit ?2,?3",nativeQuery = true)
    List<Object> getStoreHistoryOrder(long storeId,int starNumber,int endNumber);



    @Query(value = "select count(1) from (select a.id from ff_parent_order a inner join ff_order b on a.id=b.parent_id left join ff_store c on a.store_id=c.id left join ff_table d on a.table_id=d.id where a.state=0 and b.state=0 and a.store_id=?1 group by a.id) s",nativeQuery = true)
    long getStoreNopayOrderTotal(long storeId);

    @Query(value = "select c.`name` storeName,a.id orderId,a.people people,d.`name` tableName,date_format(a.create_date,'%Y-%m-%d %H:%i:%s') createDate,sum(binary b.original_price) originalPrice,sum(binary b.price) price,a.store_id storeId,a.table_id tableId,a.user_id userId,a.state state from ff_parent_order a inner join ff_order b on a.id=b.parent_id left join ff_store c on a.store_id=c.id left join ff_table d on a.table_id=d.id where a.state=0 and b.state=0 and a.store_id=?1 group by a.id order by createDate desc limit ?2,?3",nativeQuery = true)
    List<Object> getStoreNopayOrder(long storeId,int starNumber,int endNumber);

    @Query(value = "select c.`name` storeName,a.id orderId,a.people people,d.`name` tableName,date_format(a.create_date,'%Y-%m-%d %H:%i:%s') createDate,sum(binary b.original_price) originalPrice,sum(binary b.price) price,a.store_id storeId,a.table_id tableId,a.user_id userId,a.state state from ff_parent_order a inner join ff_order b on a.id=b.parent_id left join ff_store c on a.store_id=c.id left join ff_table d on a.table_id=d.id where a.store_id=?1 and a.id in ?2 and a.state=b.state group by a.id order by createDate desc",nativeQuery = true)
    List<Object> getStoreNopayOrderUpdate(long storeId,List<Long> parentId);

    @Query(value = "select count(1) from ff_order b left join ff_store c on b.store_id=c.id left join ff_table d on b.table_id=d.id where b.state=2 and b.store_id=?1",nativeQuery = true)
    long getStoreConfirmOrderTotal(long storeId);

    @Query(value = "select c.`name` storeName,b.id orderId,b.people people,d.`name` tableName,date_format(b.created_time,'%Y-%m-%d %H:%i:%s') createDate,b.original_price originalPrice,b.price price,b.store_id storeId,b.table_id tableId,b.user_id userId,b.state state from ff_order b left join ff_store c on b.store_id=c.id left join ff_table d on b.table_id=d.id where b.state=2 and b.store_id=?1 order by b.created_time desc limit ?2,?3",nativeQuery = true)
    List<Object> getStoreConfirmOrder(long storeId,int starNumber,int endNumber);

    @Query(value = "select c.`name` storeName,b.id orderId,b.people people,d.`name` tableName,date_format(b.created_time,'%Y-%m-%d %H:%i:%s') createDate,b.original_price originalPrice,b.price price,b.store_id storeId,b.table_id tableId,b.user_id userId,b.state state from ff_order b left join ff_store c on b.store_id=c.id left join ff_table d on b.table_id=d.id where b.store_id=?1 and b.id in ?2 order by b.created_time",nativeQuery = true)
    List<Object> getStoreConfirmOrderUpdate(long storeId,List<Long> subId);

    @Query(value = "select c.`name` storeName,b.id orderId,b.people people,d.`name` tableName,date_format(b.created_time,'%Y-%m-%d %H:%i:%s') createDate,b.original_price originalPrice,b.price price,b.store_id storeId,b.table_id tableId,b.user_id userId,b.state state from ff_order b left join ff_store c on b.store_id=c.id left join ff_table d on b.table_id=d.id where b.state=2 and b.user_id=?1 order by b.created_time desc limit 5",nativeQuery = true)
    List<Object> getWechatTableConfirmOrder(String userId);

    @Query(value = "select date_format(sysdate(),'%Y-%m-%d %H:%i:%s') lastUpdateDate",nativeQuery = true)
    Object getLastUpdateDate();

    //@Query(value = "select (select sum(a.people) peopleTotal from ff_parent_order a where a.store_id=?1 and (a.state=1 or a.state=5) and a.create_date>=date_format(sysdate(),'%Y-%m-%d') and a.create_date<date_format(date_add(sysdate(), INTERVAL 1 day),'%Y-%m-%d')) peopleTotal,(select sum(binary b.price) priceTotal from ff_parent_order a,ff_order b where a.store_id=?1 and a.id=b.parent_id and (a.state=1 or a.state=5) and (b.state=1 or b.state=5) and a.create_date>=date_format(sysdate(),'%Y-%m-%d') and a.create_date<date_format(date_add(sysdate(), INTERVAL 1 day),'%Y-%m-%d')) priceTotal,(select sum(c.count) menuTotal from ff_parent_order a,ff_order b,ff_map_order_menu c where a.store_id=?1 and a.id=b.parent_id and b.id=c.order_id and (a.state=1 or a.state=5) and (b.state=1 or b.state=5) and c.remove_flag=0 and a.create_date>=date_format(sysdate(),'%Y-%m-%d') and a.create_date<date_format(date_add(sysdate(), INTERVAL 1 day),'%Y-%m-%d')) menuTotal from dual",nativeQuery = true)
    @Query(value = "select sum(e.people) peopleTotal,sum(e.menuTotal) menuTotal,sum(e.priceTotal) priceTotal from (select d.parentId,d.people,sum(binary d.price) priceTotal,sum(d.menuTotal) menuTotal from (select a.id parentId,b.id subId,a.people people,b.price price,sum(c.count) menuTotal from ff_parent_order a,ff_order b,ff_map_order_menu c where a.store_id=?1 and a.id=b.parent_id and b.id=c.order_id and a.froms in ?2 and a.state in ?3 and b.state in ?3 and c.remove_flag=0 and a.create_date>=date_format(sysdate(),'%Y-%m-%d') and a.create_date<date_format(date_add(sysdate(), INTERVAL 1 day),'%Y-%m-%d') group by a.id,b.id) d group by d.parentId) e",nativeQuery = true)
    Object getTodayZbData(long storeId,List<Integer> froms,List<Integer> state);

    @Query(value = "select d.id menuId,d.name menuName,d.unit_price price,sum(c.count) menuTotal from ff_parent_order a,ff_order b,ff_map_order_menu c,ff_menu d where a.store_id=?1 and a.id=b.parent_id and b.id=c.order_id and c.menu_id=d.id  and a.froms in ?2 and a.state in ?3 and b.state in ?3 and c.remove_flag=0 and a.create_date>=date_format(sysdate(),'%Y-%m-%d') and a.create_date<date_format(date_add(sysdate(), INTERVAL 1 day),'%Y-%m-%d') group by d.id order by menuTotal desc limit 10",nativeQuery = true)
    List<Object> getTodayTopMenuData(long storeId,List<Integer> froms,List<Integer> state);

    @Query(value = "select d.parentId parentId,d.people people,d.froms froms,d.state state,date_format(min(d.created_time),'%H:%i') createDate,sum(binary d.price) priceTotal,sum(binary d.original_price) originalPriceTotal,sum(d.menuTotal) menuTotal from (select a.id parentId,b.id subId,a.people people,a.froms froms,a.state state,b.price price,b.original_price original_price,sum(c.count) menuTotal,b.created_time created_time from ff_parent_order a,ff_order b,ff_map_order_menu c where a.store_id=?1 and a.id=b.parent_id and b.id=c.order_id and a.froms in ?2 and a.state in ?3 and b.state in ?3 and c.remove_flag=0 and a.create_date>=date_format(sysdate(),'%Y-%m-%d') and a.create_date<date_format(date_add(sysdate(), INTERVAL 1 day),'%Y-%m-%d') group by a.id,b.id) d group by d.parentId order by createDate desc limit 200",nativeQuery = true)
    List<Object> getTodayOrderListData(long storeId,List<Integer> froms,List<Integer> state);

    /**
     * 同意或拒绝订单
     * @param id
     * @param state
     */
    @Modifying
    @Query("update ParentOrderModel m set m.state=?2 where m.id=?1")
    void setOrderState(Long id, int state);

    @Query(value = "select id,name,state,seating from ff_table where store_id=?1 and last_update_date>?2",nativeQuery = true)
    List<Object> getTableUpdateList(long store_id, String updateDate);

    @Query(value = "select id,name,state,seating from ff_table where store_id=?1",nativeQuery = true)
    List<Object> getTableAllList(long store_id);


}
